package com.sooth.system.datasource.service;

import com.dream.boot.impl.ServiceImpl;
import com.sooth.common.core.exception.ServiceException;
import com.sooth.common.core.utils.SpringUtils;
import com.sooth.common.encrypt.AesEncryptor;
import com.sooth.system.datasource.event.DatasourceEvent;
import com.sooth.system.datasource.util.DbUtil;
import com.sooth.system.datasource.view.SysDataSourceBo;
import com.sooth.system.datasource.view.SysDataSourceVo;
import com.sooth.system.datasource.view.TableColumnVo;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

@Slf4j
@RequiredArgsConstructor
@Service
public class SysDatasourceServiceImpl extends ServiceImpl<SysDataSourceVo, SysDataSourceBo> implements ISysDatasourceService {
    private final AesEncryptor aesEncryptor;

    @Override
    public SysDataSourceBo selectById(Object id) {
        SysDataSourceBo sysDataSourceBo = super.selectById(id);
        if (sysDataSourceBo != null) {
            sysDataSourceBo.setPassword(aesEncryptor.decrypt(sysDataSourceBo.getPassword()));
        }
        return sysDataSourceBo;
    }

    @Override
    public int insert(SysDataSourceBo sysDataSourceBo) {
        sysDataSourceBo.setPassword(aesEncryptor.encrypt(sysDataSourceBo.getPassword()));
        return super.insert(sysDataSourceBo);
    }

    @Override
    public int updateById(SysDataSourceBo sysDataSourceBo) {
        sysDataSourceBo.setPassword(aesEncryptor.encrypt(aesEncryptor.decrypt(sysDataSourceBo.getPassword())));
        int count = super.updateById(sysDataSourceBo);
        SpringUtils.context().publishEvent(new DatasourceEvent(sysDataSourceBo.getId()));
        return count;
    }

    @Override
    public int deleteById(Object id) {
        int count = super.deleteById(id);
        SpringUtils.context().publishEvent(new DatasourceEvent((Long) id));
        return count;
    }

    @Override
    public void test(SysDataSourceBo datasource) {
        Connection connection = DbUtil.getConnection(datasource.getUrl(), datasource.getUsername(), aesEncryptor.decrypt(datasource.getPassword()));
        DbUtil.close(connection);
    }

    @Override
    public List<Map<String, String>> listTable(Long datasourceId) {
        List<Map<String, String>> tableList = new ArrayList<>(64);
        SysDataSourceBo dataSource = selectById(datasourceId);
        String url = dataSource.getUrl();
        String username = dataSource.getUsername();
        String password = dataSource.getPassword();
        Connection connection = DbUtil.getConnection(url, username, password);
        try {
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet resultSet = metaData.getTables(connection.getCatalog(), connection.getSchema(), "%", new String[]{"TABLE"});
            while (resultSet.next()) {
                String table = resultSet.getString("TABLE_NAME");
                String remark = resultSet.getString("REMARKS");
                tableList.add(Map.of("table", table, "remark", remark));
            }
        } catch (SQLException e) {
            throw new ServiceException(e.getMessage());
        } finally {
            DbUtil.close(connection);
        }
        return tableList;
    }

    @Override
    public List<TableColumnVo> listField(Long datasourceId, String table) {
        List<TableColumnVo> tableColumnVoList = new ArrayList<>(64);
        SysDataSourceBo dataSource = selectById(datasourceId);
        String url = dataSource.getUrl();
        String username = dataSource.getUsername();
        String password = dataSource.getPassword();
        Connection connection = DbUtil.getConnection(url, username, password);
        try {
            DatabaseMetaData metaData = connection.getMetaData();
            Set<String> primarySet = new HashSet<>(1);
            try (ResultSet resultSet = metaData.getPrimaryKeys(connection.getCatalog(), connection.getSchema(), table)) {
                while (resultSet.next()) {
                    String column = resultSet.getString("COLUMN_NAME");
                    primarySet.add(column);
                }
            }
            try (ResultSet resultSet = metaData.getColumns(connection.getCatalog(), connection.getSchema(), table, "%")) {
                while (resultSet.next()) {
                    String column = resultSet.getString("COLUMN_NAME");
                    int dataType = resultSet.getInt("DATA_TYPE");
                    String typeName = resultSet.getString("TYPE_NAME");
                    int nullable = resultSet.getInt("NULLABLE");
                    String remark = resultSet.getString("REMARKS");
                    TableColumnVo tableColumnVo = new TableColumnVo();
                    tableColumnVo.setColumn(column);
                    tableColumnVo.setPk(primarySet.contains(column) ? 1 : 0);
                    tableColumnVo.setDataType(dataType);
                    tableColumnVo.setTypeName(typeName);
                    tableColumnVo.setNullable(nullable);
                    tableColumnVo.setRemark(remark);
                    tableColumnVoList.add(tableColumnVo);
                }
            }
        } catch (SQLException e) {
            throw new ServiceException(e.getMessage());
        } finally {
            DbUtil.close(connection);
        }
        return tableColumnVoList;
    }

    @Override
    public List<String> listPrimaryKeys(Long datasourceId, String table) {
        List<String> primaryList = new ArrayList<>(1);
        SysDataSourceBo dataSource = selectById(datasourceId);
        String url = dataSource.getUrl();
        String username = dataSource.getUsername();
        String password = dataSource.getPassword();
        Connection connection = DbUtil.getConnection(url, username, password);
        try {
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet resultSet = metaData.getPrimaryKeys(connection.getCatalog(), connection.getSchema(), table);
            while (resultSet.next()) {
                String column = resultSet.getString("COLUMN_NAME");
                primaryList.add(column);
            }
        } catch (SQLException e) {
            throw new ServiceException(e.getMessage());
        } finally {
            DbUtil.close(connection);
        }
        return primaryList;
    }
}
